6  Working with Numeric Data

6.1 Reference

Default database in this chapter is “ex”.

Other databases included in the simulation MySQL server:

• om
• ap

6.2 Numeric Data

Main Types

Integer(INT type): no decimal places.

Fixed-point(DECIMAL type): up to 9 decimal places.

• Because there are finite number of decimal places, they are exact numeric type.

Floating-point (shortly floating, DOUBLE/FLOAT type) values are numeric values with unlimited number of decimal places.

• Because there are unlimited number of decimal places, they are approximate numeric type.

Other Concepts

Precision: number of digit (e.g. 120.98 has a precision of 5, as in 5 digits in total)

Scale: number of decimal places (e.g. 120.98 has a scale of 2, as in 2 decimal places)

6.3 Numeric Data Manipulation

Managing Rounding and Scale

SELECT 
  float_value,
  ROUND(float_value, 3),
  TRUNCATE(float_value, 3),
  CEILING(float_value),
  FLOOR(float_value)
FROM float_sample

6 records

float_value	ROUND(float_value, 3)	TRUNCATE(float_value, 3)	CEILING(float_value)	FLOOR(float_value)
1.00000	1.000	0.999	1	0
1.00000	1.000	1.000	1	1
1.00000	1.000	1.000	2	1
1234.56789	1234.568	1234.567	1235	1234
999.04440	999.044	999.044	1000	999
24.04849	24.048	24.048	25	24

Note: negative values are evaluated together with their negativity.

SELECT 
  CEILING(-12.4567),
  FLOOR(-12.4567)

1 records

CEILING(-12.4567)	FLOOR(-12.4567)
-12	-13

Calcuation

SELECT 
  float_value,
  ABS(float_value),
  -- SIGN(float_value),
  SQRT(float_value),
  POWER(float_value, 3)
FROM float_sample

6 records

float_value	ABS(float_value)	SQRT(float_value)	POWER(float_value, 3)
1.00000	1.00000	1.000000	1.000000e+00
1.00000	1.00000	1.000000	1.000000e+00
1.00000	1.00000	1.000000	1.000000e+00
1234.56789	1234.56789	35.136418	1.881676e+09
999.04440	999.04440	31.607664	9.971359e+08
24.04849	24.04849	4.903926	1.390796e+04

Random Number Generation

RAND() function doesn’t require any input to work, i.e. they will generate random numbers anyways.

The integer as an argument is called “seed”. This is used to ensure everytime the query run it will create exactly the same set of random numbers.

SELECT
  float_value,
  RAND(123)
FROM float_sample

6 records

float_value	RAND(123)
1.00000	0.9277429
1.00000	0.4609716
1.00000	0.5216294
1234.56789	0.2252323
999.04440	0.5612733
24.04849	0.1306697